﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;

namespace 无线打分网站
{
    public class Analysis
    {
        public static DataTable GetTeacherList()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select teacher from TeachGrade group by  teacher");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sql.ToString());
        }
        public static DataTable GetShiYanList()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ShiYan from TeachGrade group by  ShiYan");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sql.ToString());
        }
        public static DataTable GetRoomList()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select Room from TeachGrade group by  Room");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sql.ToString());
        }
        public static DataTable GetTGradeList(string t,string s,string r,string stime,string eTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select Teacher,ShiYan,Room,");
            sql.Append("SUM(case when grade=5 then 1 else 0 end) as grade5 ,");
            sql.Append("SUM(case when grade=4 then 1 else 0 end) as grade4 ,");
            sql.Append("SUM(case when grade=3 then 1 else 0 end) as grade3 ,");
            sql.Append("SUM(case when grade=2 then 1 else 0 end) as grade2 ,");
            sql.Append("SUM(case when grade=1 then 1 else 0 end) as grade1 ,");
            sql.Append("SUM(grade) as gradesum ");
            sql.Append("from TeachGrade where (DateTime between '");
            sql.Append(stime);
            sql.Append("' and '");
            sql.Append(eTime);
            sql.Append("')");
            if (!string.IsNullOrEmpty(t))
            {
                sql.Append(" and (teacher = '");
                sql.Append(t);
                sql.Append("')");
            }
            if (!string.IsNullOrEmpty(s))
            {
                sql.Append(" and (shiyan = '");
                sql.Append(s);
                sql.Append("')");
            }
            if (!string.IsNullOrEmpty(r))
            {
                sql.Append(" and (room = '");
                sql.Append(r);
                sql.Append("')");
            }
            sql.Append(" group by Teacher,ShiYan,Room");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sql.ToString());
        }
        public static DataTable GetEGradeList(string t, string s, string r, string stime, string eTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select Teacher,ShiYan,Room,");
            sql.Append("SUM(case when grade=5 then 1 else 0 end) as grade5 ,");
            sql.Append("SUM(case when grade=4 then 1 else 0 end) as grade4 ,");
            sql.Append("SUM(case when grade=3 then 1 else 0 end) as grade3 ,");
            sql.Append("SUM(case when grade=2 then 1 else 0 end) as grade2 ,");
            sql.Append("SUM(case when grade=1 then 1 else 0 end) as grade1 ,");
            sql.Append("SUM(grade) as gradesum ");
            sql.Append("from ExperimentGrade where (DateTime between '");
            sql.Append(stime);
            sql.Append("' and '");
            sql.Append(eTime);
            sql.Append("')");
            if (!string.IsNullOrEmpty(t))
            {
                sql.Append(" and (teacher = '");
                sql.Append(t);
                sql.Append("')");
            }
            if (!string.IsNullOrEmpty(s))
            {
                sql.Append(" and (shiyan = '");
                sql.Append(s);
                sql.Append("')");
            }
            if (!string.IsNullOrEmpty(r))
            {
                sql.Append(" and (room = '");
                sql.Append(r);
                sql.Append("')");
            }
            sql.Append(" group by Teacher,ShiYan,Room");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sql.ToString());
        }
    }
}